package vn.edu.saigontech.ncms.service.impl;

import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import vn.edu.saigontech.ncms.model.info.Offering;
import vn.edu.saigontech.ncms.model.info.OfferingMember;
import vn.edu.saigontech.ncms.model.info.Receipt;
import vn.edu.saigontech.ncms.model.info.Student;
import vn.edu.saigontech.ncms.model.program.Level;
import vn.edu.saigontech.ncms.model.system.Money;
import vn.edu.saigontech.ncms.service.OfferingMemberService;
import vn.edu.saigontech.ncms.service.OfferingService;
import vn.edu.saigontech.ncms.service.ReceiptService;
import vn.edu.saigontech.ncms.service.StudentService;
import vn.edu.saigontech.ncms.service.UserService;
import vn.edu.saigontech.ncms.util.Checker;

@SuppressWarnings("unchecked")
public class ReceiptServiceImpl extends HibernateGenericService<Receipt, Long> implements ReceiptService {
	
	public Collection<Receipt> find(String studentFirstName, String studentLastName, Date studentDateOfBirth, String number, Date date, Date fromDate, Date toDate, String offeringName, Money fee, Long cashierId, String comment) {
		Criteria offeringReceiptCriteria = this.createCriteria();
		Criteria studentCriteria = offeringReceiptCriteria.createCriteria("student");		
		if(!Checker.isEmpty(studentFirstName)) {
			studentCriteria.add(Restrictions.ilike("firstName", studentFirstName, MatchMode.ANYWHERE));
		}
		if(!Checker.isEmpty(studentLastName)) {
			studentCriteria.add(Restrictions.ilike("lastName", studentLastName, MatchMode.ANYWHERE));
		}
		if(!Checker.isNull(studentDateOfBirth)) {
			studentCriteria.add(Restrictions.eq("dateOfBirth", studentDateOfBirth));
		}
		if(!Checker.isEmpty(number)) {
			offeringReceiptCriteria.add(Restrictions.ilike("number", number, MatchMode.ANYWHERE));
		}
		if(!Checker.isNull(date)) {
			offeringReceiptCriteria.add(Restrictions.eq("date", date));
		}
		if(!Checker.isNull(fromDate)) {
			offeringReceiptCriteria.add(Restrictions.ge("date", fromDate));
		}
		if(Checker.isNotNull(fromDate) && Checker.isNotNull(toDate)) {
			offeringReceiptCriteria.add(Restrictions.between("date", fromDate, toDate));
		}
		if(!Checker.isEmpty(offeringName)) {
			offeringReceiptCriteria.createCriteria("offering").add(Restrictions.ilike("name", offeringName, MatchMode.ANYWHERE));
		}
		if(!Checker.isNull(fee)) {
			offeringReceiptCriteria.add(Restrictions.eq("fee", fee));
		}
		if(!Checker.isNull(cashierId)) {
			offeringReceiptCriteria.createCriteria("cashier").add(Restrictions.eq("id", cashierId));
		}
		if(!Checker.isEmpty(comment)) {
			offeringReceiptCriteria.add(Restrictions.ilike("comment", comment, MatchMode.ANYWHERE));
		}
		return offeringReceiptCriteria.list();
	}
	
	@Override
	public Collection<Receipt> findByDate(Date fromDate, Date toDate) {
		Criteria criteria = this.createCriteria();
		if(!Checker.isNull(fromDate)) {
			criteria.add(Restrictions.ge("date", fromDate));
		}
		if(!Checker.isNull(toDate)) {
			criteria.add(Restrictions.le("date", toDate));
		}
		criteria.addOrder(Order.asc("id"));
		return criteria.list();
	}
	
	@Override
	public List<Receipt> findByDateAsc(Date fromDate, Date toDate) {
		Criteria criteria = this.createCriteria();
		if(!Checker.isNull(fromDate)) {
			criteria.add(Restrictions.ge("date", fromDate));
		}
		if(!Checker.isNull(toDate)) {
			criteria.add(Restrictions.le("date", toDate));
		}
		criteria.addOrder(Order.asc("id"));
		return criteria.list();
	}
	
	public Collection<Receipt> findByStudentId(Long studentId) {
		Criteria receiptCriteria = this.createCriteria();
		receiptCriteria.createCriteria("student").add(Restrictions.eq("id", studentId));
		return receiptCriteria.list();
	}
	
	public Receipt add(String number, Long studentId, Long offeringId, Date createdDate, Money fee, Long cashierId, String comment) {
		Receipt offeringReceipt = new Receipt();
		offeringReceipt.setNumber(number);
		offeringReceipt.setStudent(StudentService.INSTANCE.findById(studentId));
		offeringReceipt.setOffering(OfferingService.INSTANCE.findById(offeringId));
		offeringReceipt.setDate(createdDate);
		offeringReceipt.setFee(fee);
		offeringReceipt.setActive(true);
		offeringReceipt.setCashier(UserService.INSTANCE.findById(cashierId));
		offeringReceipt.setComment(comment);
		if(!Checker.isNull(super.persist(offeringReceipt))) {
			OfferingMemberService.INSTANCE.add(offeringReceipt.getId());
			return offeringReceipt;
		}
		return null;
	}

	public Receipt edit(Long receiptId, String receiptNo, Long offeringId, Date createdDate, Money fee, Long cashierId, boolean active, String comment) {
		Receipt receipt = this.findById(receiptId);
		receipt.setOffering(OfferingService.INSTANCE.findById(offeringId));
		receipt.setNumber(receiptNo);
		receipt.setDate(createdDate);
		receipt.setFee(fee);
		receipt.setCashier(UserService.INSTANCE.findById(cashierId));
		receipt.setActive(active);
		receipt.getOfferingMember().setActive(active);
		receipt.setComment(comment);
		return super.persist(receipt);
	}
	
	@Override
	public boolean deleteById(Long id) {
		return super.deleteById(id);
	}

	public Collection<Receipt> findByOfferingId(Long offeringId) {
		Criteria receiptCriteria = this.createCriteria();
		Criteria offeringCriteria = receiptCriteria.createCriteria("offering");
		Criteria studentCriteria = receiptCriteria.createCriteria("student");
		offeringCriteria.add(Restrictions.eq("id", offeringId));
		studentCriteria.addOrder(Order.asc("firstName"));
		return receiptCriteria.list();
	}

	public Collection<Receipt> findRecently() {
		Criteria receiptCriteria = this.createCriteria();
		receiptCriteria.addOrder(Order.desc("id"));
		receiptCriteria.setFirstResult(0);
		receiptCriteria.setMaxResults(5);
		return receiptCriteria.list();
	}

	@Override
	public int countByUserId(Long userId) {
		Criteria criteria = this.createCriteria();
		criteria.createCriteria("cashier").add(Restrictions.eq("id", userId));
		criteria.setProjection(Projections.rowCount());
		return (Integer) criteria.uniqueResult();
	}

	@Override
	public Student findStudentByOfferingMemberId(Long memberId) {
		Criteria criteria = this.createCriteria();
		Criteria memberCriteria = criteria.createCriteria("member");
		memberCriteria.add(Restrictions.eq("id", memberId));
		Student ret = null;
		List<Receipt> list = criteria.list();
		if(!list.isEmpty()) {
			ret = list.get(0).getStudent();
		}
		return ret;
	}

	@Override
	public Level findLevelByOfferingMember(OfferingMember member) {
		Criteria criteria = this.createCriteria();
//		criteria.add(Restrictions.eq("member", member));
		Criteria memberCriteria = criteria.createCriteria("member");
		memberCriteria.add(Restrictions.eq("id", member.getId()));
		List<Receipt> list = criteria.list();
		Level ret = null;
		if(!list.isEmpty()) {
			ret = list.get(0).getOffering().getLevel();
		}
		return ret;
	}

	@Override
	public Offering findOfferingByOfferingMember(OfferingMember member) {
		Criteria criteria = this.createCriteria();
//		criteria.add(Restrictions.eq("member", member));
		Criteria memberCriteria = criteria.createCriteria("member");
		memberCriteria.add(Restrictions.eq("id", member.getId()));
		List<Receipt> list = criteria.list();
		Offering ret = null;
		if(!list.isEmpty()) {
			ret = list.get(0).getOffering();
		}
		return ret;
	}

	@Override
	public Collection<Receipt> findAllRecently() {
		String queryString = "SELECT * FROM ncms_receipt WHERE " +
				"extract(month from date) in (extract(month from current_date)-1, extract(month from current_date)) " +
				"AND extract(year from date) = extract(year from current_date)";
		SQLQuery query = getSession().createSQLQuery(queryString);
		query.addEntity(Receipt.class);
		return query.list();
	}

	@Override
	public Object[] findInfoByMaintenanceId(Long maintenanceId) {
		String sql = "SELECT re.date, offer.name, offer.start_date, re.number " +
				"FROM ncms_receipt AS re, ncms_offering AS offer, ncms_receipt_maintenance AS rm " +
				"WHERE rm.id=? " +
				"AND re.receipt_maintenance = rm.id " +
				"AND re.offering_id = offer.id";
		SQLQuery query = getSession().createSQLQuery(sql);
		
		query = query.addScalar("date", Hibernate.DATE);
		query = query.addScalar("name", Hibernate.STRING);
		query = query.addScalar("start_date", Hibernate.DATE);
		query = query.addScalar("number", Hibernate.STRING);
		
		return (Object[]) query.setLong(0, maintenanceId).uniqueResult();
	}
}